package com.btpn.custody.dao.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.btpn.custody.common.Constant;
import com.btpn.custody.common.LogEvent;
import com.btpn.custody.dao.ITL_GLOBAL;
import com.btpn.custody.entity.TL_GLOBAL;

/*
 * PURPOSE : create, read, update, & delete to TBL_M_PARAM
 * Default value for SLA is 3 days
 */
@Component("parameterSLA")
public class ParameterSLA {

	@Autowired
	private DataSource CustodyDataSource;
	
	@Autowired
	private ITL_GLOBAL TL_GLOBAL_DaoHibernate;
	
	private final org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(this.getClass());
	
	public Integer create(String type, String key, Integer value, String user) {
			
		Integer result = null;
		
		Connection cmtConn = null;
		Statement cmtStmt = null;
		try {
			cmtConn = CustodyDataSource.getConnection();
			cmtStmt = cmtConn.createStatement();
		
			cmtStmt.executeUpdate("INSERT INTO " + Constant.CMT_M_PARAM + " VALUES('" + type + "','" + key + "','" + value + "')");
						
			// add to TL_GLOBAL
			TL_GLOBAL_DaoHibernate.create(null, Constant.ACTION__CREATE, TL_GLOBAL.GROUP_2, user, "First create SLA : " + value);
			
			result = value;
		} catch (SQLException e) {
			logger.error(LogEvent.GENERAL, e);
		} finally {
			if (cmtStmt!=null) {
				try {
					cmtStmt.close();
				} catch (SQLException e) {
					logger.error(LogEvent.STATEMENT__CLOSE, e);
				}
			}
			if (cmtConn != null) {
				try {
					cmtConn.close();
				} catch (SQLException e) {
					logger.error(LogEvent.CONNECTION__CLOSE, e);
				}
			}
		}		
		return result;
	}
	
	public Integer readValueByKey(String type, String key) {
		
		Integer result = null;
		
		Connection cmtConn = null;
		Statement cmtStmt = null;
		ResultSet rs = null;
		try {
			cmtConn = CustodyDataSource.getConnection();
			cmtStmt = cmtConn.createStatement();
		
			rs = cmtStmt.executeQuery("SELECT * FROM " + Constant.CMT_M_PARAM + " WHERE PARAM_TYPE='" + type + "' AND PARAM_CODE='" + key + "'");
			while (rs.next()) {
				result = rs.getInt("PARAM_DESC");
			}
			
		} catch (SQLException e) {
			logger.error(LogEvent.GENERAL, e);
			//1. error get from db, return 0
			//2. error not found from db, return null
		} finally {
			if (rs!=null) {
				try {
					rs.close();rs=null;
				} catch (SQLException e) {
					logger.error(LogEvent.STATEMENT__CLOSE, e);
				}
			}
			if (cmtStmt!=null) {
				try {
					cmtStmt.close();cmtStmt=null;
				} catch (SQLException e) {
					logger.error(LogEvent.STATEMENT__CLOSE, e);
				}
			}
			if (cmtConn != null) {
				try {
					cmtConn.close();cmtConn=null;
				} catch (SQLException e) {
					logger.error(LogEvent.CONNECTION__CLOSE, e);
				}
			}
		}
		return result;
	}
	
	public Integer update(String type, String key, Integer value, String user) {
		
		Integer result = null;
		Integer last = readValueByKey(Constant.P0004, Constant.PARAM_SLA);
		
		Connection cmtConn = null;
		Statement cmtStmt = null;
		try {
			cmtConn = CustodyDataSource.getConnection();
			cmtStmt = cmtConn.createStatement();
		
			cmtStmt.executeUpdate("UPDATE " + Constant.CMT_M_PARAM + " SET PARAM_DESC='" + value + "' WHERE PARAM_TYPE='" + type + "' AND PARAM_CODE='" + key + "'");

			// add to TL_GLOBAL
			TL_GLOBAL_DaoHibernate.create(null, Constant.ACTION__UPDATE, TL_GLOBAL.GROUP_2, user, "from " + last + " to " + value);	
			
			result = value;
		} catch (SQLException e) {
			logger.error(LogEvent.GENERAL, e);
		} finally {
			if (cmtStmt!=null) {
				try {
					cmtStmt.close();
				} catch (SQLException e) {
					logger.error(LogEvent.STATEMENT__CLOSE, e);
				}
			}
			if (cmtConn != null) {
				try {
					cmtConn.close();
				} catch (SQLException e) {
					logger.error(LogEvent.CONNECTION__CLOSE, e);
				}
			}
		}
		return result;
	}

	// delete
}
